All Questions
Tagged with entity-attribute-value indexing
16 questions
1
vote
1
answer
155
views
How to correctly index fields/groups of fields in a mysql database?
I really need advice on how to properly optimize mysql database queries. I'm afraid to spoil everything at the very beginning, which will turn out to be a big headache for me later.
There is a catalog ...
- 13
1
vote
1
answer
2k
views
How to avoid performance degradation when run query with cast in where clause?
I have a table with 2 varchar columns (name and value)
and I have such a query:
select * from attribute
where name = 'width' and cast( value as integer) > 12
This query works but I suppose there ...
- 37.1k
0
votes
1
answer
77
views
Optimize heavy Mysql query
I am working with a heavy query that sometimes takes more than 10 minutes to complete.
I would like to know if there is any optimization for this query and improve the process time.
SELECT
data....
- 1
0
votes
1
answer
266
views
Best way to search properties in MySQL
Hi have a table of users where we store some top level fields like userid, createdOn, email
And a second table called properties that stores a key value pair of additional properties. Like userid, key,...
- 565
1
vote
0
answers
24
views
MySQL EAV Database: Indexes not being utilised for queries using OR with LEFT JOINs (Wordpress DB) [duplicate]
We have a query on a Wordpress site (EAV database) that is very slow because it's failing to use the relevant index. Here's a simplified version of the offending query:
SELECT p.ID FROM wp_posts p
...
- 1,743
0
votes
0
answers
350
views
Index a MySQL table to search for any columns combination
I am developing a website for car ads. The search is quite complicated as you can search for cars by up to 100 filters (make, model, engine, hp, color, year, 4X4, ..... ).
I have two models of how I ...
- 18.9k
0
votes
1
answer
46
views
Improve JOINED SQL Query efficiency
I have a SQL query which is taking a long time to load.
I think I need to add some indexes but I'm not sure what indexes I should add.
This is the main SQL that is executed:
SQL FIddle: http://...
- 3,315
0
votes
1
answer
249
views
Database indices for log table
I need to create log table in a database. Each log will have various parameters. This is the table design I've come up.
Table Log
============
ID(INT)
Action(INT)
Created(TIMESTAMP)
Table ...
- 451
5
votes
1
answer
6k
views
Why are references to wp_postmeta so slow?
Fetching of attributes in WordPress (using MySQL) seems to be slower than necessary.
(This is a self-answered question, so proceed to my answer.)
- 142k
0
votes
1
answer
2k
views
MySQL self join performance: fact or just bad indexing?
As an example: I'm having a database to detect visitor (bots, etc) and since not every visitor have the same amount of 'credential' I made a 'dynamic' table like so: see fiddle: http://sqlfiddle.com/#!...
- 1,377
0
votes
1
answer
37
views
Join query on tables
I'm running 2 queries on a table, both of them are the same in "Where" clause, so I beleive they should return same values.
My first query is:
select B.NameDastgahID, B.ZarfiateHamleBar, B....
- 3
1
vote
1
answer
43
views
Indexing columns for faster querying in MySQL 5.6 or higher
I'm building a real estate app. I have a table called properties which is like the main table that has all common columns (10 columns) for all types of properties (lands, apartments, ... etc) and then ...
- 560
1
vote
1
answer
247
views
MySQL Index on TEXT Column not Effective
I've got a fairly straightforward table for storing key:value pairs, set up like:
CREATE TABLE `assetProperties` (
`propertyKey` varchar(255) NOT NULL,
`propertyValue` text NOT NULL,
`id` ...
- 54.8k
0
votes
1
answer
1k
views
MySQL reduce index size for huge table
For my online shop I've a table, that I'm using for search:
CREATE TABLE `store_search` (
`term` varchar(50) NOT NULL DEFAULT '',
`content_id` int(10) unsigned NOT NULL,
`type` enum('keyword','...
- 7,700
3
votes
0
answers
145
views
Multiple attributes to be indexed for unstructured objects
I asked similar question in context of a NOSQL DB on SO Indexing on several fields in mongoDB, soon to realize its not to do with any DB, but a general design problem. I'll try to formulate the query ...
- 1,514
2
votes
1
answer
1k
views
SQL EAV structure with Indexed views
I'm developing a general document workflow system with each document having different attributes but they share the same outline and goes through the same workflow.
So instead of creating a table ...
- 41