| Forum: MySQL |
|---|
| Topic: creating index is not always good |
|---|
| Re: creating index is not always good [message #3476 is a reply to message #3401 ] |
Fri, 29 August 2008 03:53 |
 chrisbolt
Messages: 1
Registered: August 2008 |
Junior Member |
|
|
Before you added the cat index it could use the id index for the ORDER BY. If you created a composite index on (cat, id) it would be just as fast, if not faster.
And if your query is already running in 0.00 seconds, why are you adding an index?
|
|
|
| Re: creating index is not always good [message #3477 is a reply to message #3476 ] |
Fri, 29 August 2008 11:20 |
tera7
Messages: 2
Registered: August 2008 |
Junior Member |
|
|
Yes my men i had a composite index and it was very slow cause used the id for filesort and that did a lot of time.My thoughts is that sorting is faster to be done in indexed id's using them as keys.In my example with composite indexes just takes the cat key for where and then makes filesort to id's which is the slow action.
I did it just for testing to compare my databases cause other databases i have are faster with composite indexes ,but that is happening cause is more complex the data handling through php, so we make all queries little bit slower, but none of them slow enough to hurt the speed of my site(cause i have seen that a slow query can push all queries to be slower than they really are).
Thanks
|
|
|
| Topic: replication of load data infile |
|---|
| Re: replication of load data infile [message #3478 is a reply to message #1876 ] |
Fri, 29 August 2008 15:53 |
RichardBronosky
Messages: 20
Registered: June 2007 |
Junior Member |
|
|
I don't believe there is an option for that, but I have had similar needs and solved it with a interim load. Since the interim load effectively doubles the I/O and processing required, I use a dev server. Here is a pseudo-transcript of what the process may look like:
workstation:~$ scp new_data.csv devbox:/dev/shm #because I like using tmpfs (~RAMDISK) for this
workstation:~$ ssh devbox
devbox:~$ mysqldump -h prodbox --no-data schema_name table_name | mysql test_schema
devbox:~$ mysql -e "create schema temp_schema; use temp_schema; load data local infile '/dev/shm/new_data.csv' into table table_name"
devbox:~$ mysqldump --no-create-info temp_schema table_name | mysql -h prodbox schema_name
devbox:~$ mysql -e "drop schema temp_schema"
Another alternative which I have used in the past is to write a custom AWK script that converts each line of the csv file into an insert statement. I'll post it here if I can find it.
.+# Richard Bronosky #+.
MySQL DBA, MythTV User
|
|
|
Members
Search
Help
Register
Login
Home
